package com.fyj.utils;

import lombok.extern.slf4j.Slf4j;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.locks.ReentrantLock;

@Slf4j
public class JdbcUtils {


    /**
     * 链接jdbc
     *
     * @return
     */
    public static Connection getConnection(Map dataSourceMap) {
        String driver = (String) dataSourceMap.get("driver");
        String url = (String) dataSourceMap.get("url");
        String username = (String) dataSourceMap.get("username");
        String password = (String) dataSourceMap.get("password");
        Connection connection = null;
        try {
            Class.forName(driver);
            connection = DriverManager.getConnection(url, username, password);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return connection;
    }

    /**
     * jdbc关流
     *
     * @param resultSet
     * @param prepareStatement
     * @param connection
     */
    public static void close(ResultSet resultSet, PreparedStatement prepareStatement, Connection connection) {
        try {
            if (resultSet != null) resultSet.close();
            if (prepareStatement != null) prepareStatement.close();
            if (connection != null) connection.close();
        } catch (Exception exception) {
            log.info("" + exception);
        }

    }


    /**
     * 扫描表结构
     *
     * @param dataSourceMap 数据源
     * @param tableName     表名
     * @param fileds        扫描字段
     * @return
     * @throws SQLException
     */
    public static StringBuffer scanningSourceDatabase(Map dataSourceMap, String databaseName, String tableName, String[] fileds) throws SQLException {
        Connection connection = getConnection(dataSourceMap);
        DatabaseMetaData metaData = connection.getMetaData();
        StringBuffer filedDeatil = new StringBuffer();
        ResultSet primaryKeys = metaData.getPrimaryKeys(databaseName, null, tableName);
        if (null == fileds) {           //为空则为整库迁移
            ResultSet columns = metaData.getColumns(null, null, tableName, null);
            filedDeatil = filedDeatil(columns, primaryKeys);

        } else {                         //指定字段迁移
            for (String filed : fileds) {
                ResultSet columns = metaData.getColumns(null, null, tableName, filed);
                filedDeatil.append(filedDeatil(columns, primaryKeys));
            }
        }
        primaryKeys.close();
        close(null, null, connection);
        return filedDeatil;
    }

    /**
     * 建表
     *
     * @param dataSourceMap  数据源
     * @param tableName      表名
     * @param filedCreateSql 建表语句
     * @return
     */
    public static void createTable(Map dataSourceMap, String tableName, StringBuffer filedCreateSql) throws SQLException {
        String existsSql = "DROP TABLE IF EXISTS " + tableName;
        String createsql = "CREATE TABLE " + tableName + " ( " +
                filedCreateSql + " ) " +
                "ENGINE=InnoDB DEFAULT CHARSET=utf8;";
        Connection connection = getConnection(dataSourceMap);
        Statement statement = connection.createStatement();
        statement.addBatch(existsSql);
        statement.addBatch(createsql);
        statement.executeBatch();

    }

    /**
     * 字段细节拼接
     *
     * @param columns
     * @param primaryKeys
     * @return
     * @throws SQLException
     */
    public static StringBuffer filedDeatil(ResultSet columns, ResultSet primaryKeys) throws SQLException {
        String columnName = "";  //字段名
        StringBuffer columnDetail = new StringBuffer();//字段细节
        String primary_column_name = "";

        while (primaryKeys.next()) {     //主键
            primary_column_name = primaryKeys.getString("COLUMN_NAME");//主键字段名
        }
        while (columns.next()) {
            columnName = columns.getString("COLUMN_NAME");  //字段名
            columnDetail.append("`" + columnName + "` ");//字段名
            String type_name = columns.getString("TYPE_NAME");//字段类型
            columnDetail.append(type_name.toLowerCase() + " ");
            if (!"LONGTEXT".equals(type_name)) {
                int decimal_digits = columns.getInt("DECIMAL_DIGITS");
                if (decimal_digits > 0) {      //是否有小数
                    columnDetail.append("(" + columns.getInt("COLUMN_SIZE") + "," + decimal_digits + ") ");//字段长度
                } else {
                    columnDetail.append("(" + columns.getInt("COLUMN_SIZE") + ") ");//字段长度
                }
            }
            if (columnName.equals(primary_column_name)) {
                columnDetail.append("AUTO_INCREMENT ");
            }
            int nullable = columns.getInt("NULLABLE");
            if (nullable > 0) {            //是否可以为空 0标示不能为空 1可以是空
                columnDetail.append("DEFAULT NULL, ");
            } else {
                columnDetail.append("NOT NULL,");
            }

        }
        if (columnDetail.indexOf(primary_column_name) > 0) {
            columnDetail.append("PRIMARY KEY(`" + primary_column_name + "`)");//主键
        }

        return columnDetail;
    }


    /**
     * 查询源数据库
     * 并返回插入语句
     *
     * @param dataSourceMap 数据源
     * @param tableName     表名
     * @param fileds        指定字段信息
     * @return
     * @throws SQLException
     */
    public static List<String> getSourceData(Map dataSourceMap, String tableName, String newTableName, String fileds) throws SQLException {
        List<String> insertSqlList = new ArrayList<>();
        Connection connection = getConnection(dataSourceMap);
        String deaf = fileds;
        if ("".equals(fileds)) {    //指定字段信息
            deaf = "*";
        }
        String sql = "SELECT " + deaf + " FROM " + tableName;
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet executeQuery = preparedStatement.executeQuery();
        ResultSetMetaData metaData = executeQuery.getMetaData();
        int columnCount = metaData.getColumnCount();
        List<String> columnType = new ArrayList<>();
        //获取字段名
        for (int i = 1; i <= columnCount; i++) {
            String filed = metaData.getColumnLabel(i);
            columnType.add(filed);
        }
        //拼接Insert
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("INSERT INTO `" + newTableName + "` ");
        stringBuffer.append("(");
        for (String columntype : columnType) {
            stringBuffer.append("`" + columntype + "`, ");
        }
        int lastIndexOf = stringBuffer.lastIndexOf("`,");
        String substring = stringBuffer.substring(0, lastIndexOf + 1);
        stringBuffer.setLength(0);
        //获取字段值
        while (executeQuery.next()) {
            stringBuffer.append(substring);
            stringBuffer.append(") VALUES ( ");
            for (int i = 1; i <= columnCount; i++) {
                stringBuffer.append(" '" + executeQuery.getObject(i) + "',");
            }
            int index = stringBuffer.lastIndexOf(",");
            String insertSql = stringBuffer.substring(0, index);
            stringBuffer.setLength(0);
            stringBuffer.append(insertSql);
            stringBuffer.append(");");
            insertSqlList.add(stringBuffer.toString());
            stringBuffer.setLength(0);
        }
        return insertSqlList;
    }

    /**
     * 目标数据库添加
     *
     * @param dataSourceMap
     */
    public static void insertTargetData(Map dataSourceMap, List<String> insertSqls) throws SQLException {
        Connection connection = getConnection(dataSourceMap);
        Statement statement = connection.createStatement();
        for (String insertSql : insertSqls) {
            statement.addBatch(insertSql);
        }
        statement.executeBatch();
    }


    /**
     * 获取记录数
     *
     * @param dataSourceMap
     * @return
     */
    public static long getCountFromSourceData(Map dataSourceMap, String tableName) throws SQLException {
        long count = 0;
        Connection connection = getConnection(dataSourceMap);
        PreparedStatement preparedStatement = connection.prepareStatement("SELECT COUNT(1) FROM " + tableName);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            count = resultSet.getInt(1);
        }
        return count;
    }


}